﻿/****************************** Module Header ******************************\
 * Module Name:  SQLiteHelper.cs
 * Module Version:  1.0.0 (2013-6-5)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database. It supplies public method to execute 
 * a sql command or get a common connection or get the database schema infomation.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Xml.Linq;
using System.Data.Common;
using System.Text;
using System.Data.SQLite;

namespace UCL.Data
{
    /// <summary>
    /// SQLite助理类
    /// </summary>
    public class SQLiteHelper : IDatabaseHelper
    {
        /// <summary>
        /// 获取或设置当前连接的数据库名称
        /// </summary>
        public string DatabaseName { get; set; }

        /// <summary>
        /// 获取或设置当前连接的数据库服务器名称
        /// </summary>
        public string ServerName { get; set; }

        /// <summary>
        /// 获取或设置当前处理的数据表名称
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 获取或设置数据库连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 获取数据源提供程序名称
        /// </summary>
        public string ProviderName { get { return "System.Data.SQLite"; } }

        /// <summary>
        /// 获取或设置消息收集器
        /// </summary>
        public IList<string> MessageSet { get; set; }

        /// <summary>
        /// 获取或设置是否启用调试模式,如果启用，则错误消息将包含敏感信息
        /// </summary>
        public bool IsDebug { get; set; }

        /// <summary>
        /// 构造方法
        /// </summary>
        public SQLiteHelper(string connectionString)
        {
            this.ConnectionString = connectionString;
            this.MessageSet = new List<string>();
        }

        #region 数据库普通操纵方法

        /// <summary>
        /// 执行一条命令并返回受影响的行数
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, DbParameter[] paras = null)
        {
            if (string.IsNullOrEmpty(commandText)) return 0;
            int intOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    conn.Open();
                    try
                    {
                        intOut = cmd.ExecuteNonQuery();
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return intOut;
        }

        /// <summary>
        /// 执行命令并返回一个数据表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, DbParameter[] paras = null)
        {
            DataTable dt = new DataTable();
            if (string.IsNullOrEmpty(commandText)) return dt;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    DbDataAdapter dbda = new SQLiteDataAdapter();
                    dbda.SelectCommand = cmd;
                    conn.Open();
                    try
                    {
                        dbda.Fill(dt);
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取分页数据表 2013-6-10 by sutroon
        /// </summary>
        /// <param name="fromsql">如：from tmytest where State=1</param>
        /// <param name="orderby">如：CreatedTime desc</param>
        /// <param name="fields">如：*</param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable ExecutePagingDataTable(string fromsql, string orderby, string fields, out int recordcount, out int pagecount, int pageid = 1, int pagesize = 20)
        {
            recordcount = Convert.ToInt32(this.GetValue(string.Format("select count(0) {0};", fromsql)));
            pagecount = recordcount.Ceil(pagesize);
            if (!orderby.Trim().ToLower().StartsWith("order by ")) orderby = string.Format(" order by {0} ", orderby);
            return this.ExecuteDataTable(string.Format("select {0} {1} {2} limit {3},{4};", fields, fromsql, orderby, ((pageid - 1) * pagesize), pagesize));
        }

        /// <summary>
        /// 执行命令并返回一个数据集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras">参数列表</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string commandText, DbParameter[] paras = null)
        {
            DataSet ds = new DataSet();
            if (string.IsNullOrEmpty(commandText)) return ds;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = commandText;
                    if (paras != null) cmd.Parameters.AddRange(paras);
                    DbDataAdapter dbda = new SQLiteDataAdapter();
                    dbda.SelectCommand = cmd;
                    conn.Open(); try
                    {
                        dbda.Fill(ds);
                    }
                    catch (DbException ex)
                    {
                        if (IsDebug) MessageSet.Add(commandText);
                        MessageSet.Add(ex.Message);
                    }
                }
            }
            return ds;
        }

        /// <summary>
        /// 递归获取分类数据表,数据主要用于DropDownList控件
        /// </summary>
        /// <param name="sqlQuery">查询语句,如：select id,name from catalog where parentid={0} and extags='album' order by Ordinal</param>
        /// <param name="parentID">父编号,一般是0</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '_'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        /// <param name="topID"></param>
        /// <param name="topName"></param>
        /// <returns></returns>
        public DataTable ExecuteTreeTable(string sqlQuery, string parentID, char paddingChar = '_', int loopCount = 0, string topID = "0", string topName = "请选择")
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("Level");
            if (!string.IsNullOrEmpty(topName))
            {
                dt.Rows.Add(topID, topName, 0);
            }
            ExecuteTreeTable_Func(sqlQuery, parentID, ref dt, paddingChar, loopCount);
            return dt;
        }
        // ExecuteTreeTable递归方法
        private void ExecuteTreeTable_Func(string sqlQuery, string parentID, ref DataTable dt, char paddingChar, int loopCount)
        {
            bool isTree = false;
            string sqlSearch = sqlQuery;
            if (sqlQuery.Contains('{'))
            {
                isTree = true;
                sqlSearch = sqlQuery.Replace("{0}", parentID);
            }

            using (DbConnection conn = GetDbConnection())
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlSearch;
                conn.Open();
                int i = loopCount;
                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        dt.Rows.Add(dr[0].ToString(), "".PadRight(i, paddingChar) + dr[1].ToString(), i.ToString());
                        if (isTree) ExecuteTreeTable_Func(sqlQuery, dr[0].ToString(), ref dt, paddingChar, ++loopCount);
                    }
                }
            }
        }

        /// <summary>
        /// 获取当前节点的所有子节点(包含节点关联的元素)和递归父节点树形列表
        /// <para>例如：ExecuteTreeXml("Select ID, Name from tUSerDepartment where ParentID='{0}'", "093F207D-634B-4717-AB50-91A21F035083", "Select AgentID, Name from tUserMember where DepartmentID='{0}'", "select ID, Name, ParentID from tUserDepartment where ID='{0}'"));</para>
        /// </summary>
        /// <param name="sqlChildFmt">如：Select ID, Name from tUSerDepartment where ParentID='{0}'</param>
        /// <param name="nodeID">当前节点的值</param>
        /// <param name="sqlfmtItems">如：Select AgentID, Name, Face, State from tUserMember where DepartmentID='{0}' (DepartmentID是外键，对应部门表的主键)</param>
        /// <param name="sqlfmtParent">如：select ID, Name, ParentID from tUserDepartment where ID='{0}' (需要通过Parent来追溯父级链，并且ParentID必需是第三个查询的字段！)</param>
        /// <returns>树形XML字符串</returns>
        /// <remarks>
        /// 2013-9-5 1.0.0 by sutroon 创建
        /// 2013-9-9 2.0.0 by sutroon 重构,新增父级链表重塑和子集项目关联
        /// 2013-10-29 2.0.1 by sutroon 支持字段映射到属性功能
        /// </remarks>
        public string ExecuteTreeXml(string sqlChildFmt, string nodeID, string sqlfmtItems = "", string sqlfmtParent = "")
        {
            StringBuilder sb = new StringBuilder(), sbChild = new StringBuilder(), sbParent = new StringBuilder();
            sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?><root>");
            if (!string.IsNullOrEmpty(sqlChildFmt)) ExecuteTreeXmlForChild_Func(sqlChildFmt, nodeID, ref sbChild, sqlfmtItems);
            if (string.IsNullOrEmpty(sqlfmtParent))
            {
                sb.Append(sbChild.ToString());
            }
            else
            {
                sbParent.Append(sbChild.ToString());
                ExecuteTreeXmlForParent_Func(sqlfmtParent, nodeID, ref sbParent);
                sb.Append(sbParent.ToString());
            }
            sb.Append("</root>");
            return sb.ToString();
        }
        // 获取所有子集以及子集中的所有项目的方法
        private void ExecuteTreeXmlForChild_Func(string sqlfmt, string pid, ref StringBuilder sb, string sqlfmtItems = "")
        {
            if (string.IsNullOrEmpty(sqlfmt)) return;
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            DataTable dt2;
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.AppendFormat("<node {0}>", r.ToAttributeString());
                    if (!string.IsNullOrEmpty(sqlfmtItems))
                    {
                        dt2 = ExecuteDataTable(string.Format(sqlfmtItems, r[0]));
                        if (dt2.Rows.Count > 0)
                        {
                            foreach (DataRow r2 in dt2.Rows)
                            {
                                sb.AppendFormat("<node {0} />", r2.ToAttributeString());
                            }
                        }
                    }
                    ExecuteTreeXmlForChild_Func(sqlfmt, r[0].ToString(), ref sb);
                    sb.Append("</node>");
                }
            }
        }
        // 获取当前节点所有父节点链表的方法
        private void ExecuteTreeXmlForParent_Func(string sqlfmt, string pid, ref StringBuilder sb)
        {
            if (string.IsNullOrEmpty(sqlfmt)) return;
            DataTable dt = ExecuteDataTable(string.Format(sqlfmt, pid));
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    sb.Insert(0, string.Format("<node {0}>", r.ToAttributeString()));
                    ExecuteTreeXmlForParent_Func(sqlfmt, r[2].ToString(), ref sb);
                    sb.Append("</node>");
                }
            }
        }

        /// <summary>
        /// 获取指定行的所有值的列表
        /// </summary>
        /// <typeparam name="V">指定行的数据类型</typeparam>
        /// <param name="sqlQuery">查询语句</param>
        /// <returns></returns>
        public IList<V> GetColumnValues<V>(string sqlQuery)
        {
            IDataReader idr = this.ExecuteDataTable(sqlQuery).CreateDataReader();
            IList<V> list = new List<V>();
            while (idr.Read())
            {
                list.Add((V)Convert.ChangeType(idr[0], typeof(V)));
            }
            return list;
        }

        /// <summary>
        /// 获取值 2013-6-9 by sutroon
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetValue(string sql, object defv = null)
        {
            using (DbConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Connection.Open();
                    object v = cmd.ExecuteScalar();
                    return v == DBNull.Value ? defv : v;
                }
            }
        }

        public string SelectString(string tableName, string search, string fields = "*", string defv = "")
        {
            if (!string.IsNullOrEmpty(search)) search = " where " + search;
            object v = this.GetValue(string.Format("select {0} from {1}{2}", fields, tableName, search));
            return v == null ? defv : v.ToString();
        }

        /// <summary>
        /// 导出Sql数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public string SO_ExportTableDataAsSql(string tableName, string condition = "")
        {
            StringBuilder sb = new StringBuilder();
            using (DbDataReader ddr = this.ExecuteDataTable("select * from {0} {1}".Fmt(tableName, Sql_SetConditionSql(condition))).CreateDataReader())
            {
                if (!ddr.HasRows) return "";

                StringBuilder sbFields;
                StringBuilder sbValues;

                sb.AppendLine();
                sb.AppendLine("-- set identity_insert `" + tableName + "` on");
                sb.AppendLine();
                while (ddr.Read())
                {
                    sbValues = new StringBuilder();
                    sbFields = new StringBuilder();
                    for (int i = 0; i < ddr.FieldCount; i++)
                    {
                        if (ddr[i] == DBNull.Value) continue;
                        sbFields.AppendFormat("`{0}`,", ddr.GetName(i));
                        string s = FieldTypeToClrTypeName(ddr.GetDataTypeName(i));
                        switch (FieldTypeToClrTypeName(ddr.GetDataTypeName(i), false))
                        {
                            case "string":
                            case "DateTime":
                            case "xml":
                            case "image":
                            case "Guid":
                                sbValues.AppendFormat("'{0}',", ddr[i]);
                                break;
                            case "bool":
                                sbValues.AppendFormat("{0},", (ddr[i].ToString() == "True" ? 1 : 0));
                                break;
                            default:
                                sbValues.AppendFormat("{0},", ddr[i]);
                                break;
                        }
                    }
                    sb.AppendFormat("insert into `{0}` ({1}) values ({2});", tableName, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
                    sb.AppendLine();
                    sb.AppendLine();
                }
            }
            return sb.ToString();
        }

        /// <summary>
        /// 导出所有Sql数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_ExportTableDataAsSqls(params string[] tableNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            DbDataReader ddr;
            StringBuilder sb, sbFields, sbValues;
            foreach (string s in tableNames)
            {
                using (ddr = this.ExecuteDataTable("select * from {0}".Fmt(s)).CreateDataReader())
                {
                    if (!ddr.HasRows) continue;

                    sb = new StringBuilder();

                    sb.AppendLine();
                    sb.AppendLine("-- set identity_insert `" + s + "` on");
                    sb.AppendLine();
                    while (ddr.Read())
                    {
                        sbValues = new StringBuilder();
                        sbFields = new StringBuilder();
                        for (int i = 0; i < ddr.FieldCount; i++)
                        {
                            if (ddr[i] == DBNull.Value) continue;
                            sbFields.AppendFormat("`{0}`,", ddr.GetName(i));
                            switch (FieldTypeToClrTypeName(ddr.GetDataTypeName(i), false))
                            {
                                case "string":
                                case "DateTime":
                                case "xml":
                                case "image":
                                case "Guid":
                                    sbValues.AppendFormat("'{0}',", ddr[i]);
                                    break;
                                case "bool":
                                    sbValues.AppendFormat("{0},", (ddr[i].ToString() == "True" ? 1 : 0));
                                    break;
                                default:
                                    sbValues.AppendFormat("{0},", ddr[i]);
                                    break;
                            }
                        }
                        sb.AppendFormat("insert into `{0}` ({1}) values ({2});", s, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
                        sb.AppendLine();
                        sb.AppendLine();
                    }
                    dict.Add(s, sb.ToString());
                }
            }
            return dict;
        }

        /// <summary>
        /// 更新数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public int Update(string tableName, string fields, string search)
        {
            return this.ExecuteNonQuery(string.Format("update {0} set {1} where {2}", tableName, fields, search));
        }

        /// <summary>
        /// 插入数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public int Insert(string tableName, string fields, string values)
        {
            return this.ExecuteNonQuery(string.Format("insert into {0} ({1}) values ({2})", tableName, fields, values));
        }

        /// <summary>
        /// 删除数据库记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        public int Delete(string tableName, string search)
        {
            if (!string.IsNullOrEmpty(search)) search = " where " + search;
            return this.ExecuteNonQuery(string.Format("delete from {0}{1}", tableName, search));
        }

        /// <summary>
        /// 判断记录是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks>2013-11-3 by sutroon</remarks>
        public bool Exists(string tableName, string search, string field = "ID")
        {
            return Count(tableName, search, field) > 0;
        }

        /// <summary>
        /// 查询记录数量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        /// <remarks>2013-11-3 by sutroon</remarks>
        public int Count(string tableName, string search, string field = "ID")
        {
            return (int)this.GetValue(string.Format("select count({0}) from {1}{2}", field, tableName, search));
        }

        /// <summary>
        /// 查询数据表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public DataTable SelectDataTable(string tableName, string search, string fields = "*")
        {
            return ExecuteDataTable(string.Format("select {0} from {1}{2}{3}", fields, tableName, string.IsNullOrEmpty(search) ? "" : " where ", search));
        }

        /// <summary>
        /// 上移 2013-6-10 by sutroon
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="id"></param>
        /// <param name="idPrev"></param>
        public void MoveUp(string tablename, int id, int idPrev)
        {
            int ordinal = this.ExecuteDataTable(string.Format("select Ordinal from {0} where ID={1};", tablename, idPrev)).Rows[0][0].ToString().ToInt();

            this.Update(tablename, "Ordinal=" + (ordinal + 1), "ID=" + idPrev);
            this.Update(tablename, "Ordinal=" + ordinal, "ID=" + id);
        }
        public void MoveDown(string tablename, int id, int idNext)
        {
            int ordinal = this.ExecuteDataTable(string.Format("select Ordinal from {0} where ID={1};", tablename, idNext)).Rows[0][0].ToString().ToInt();

            this.Update(tablename, "Ordinal=" + (ordinal - 1), "ID=" + idNext);
            this.Update(tablename, "Ordinal=" + ordinal, "ID=" + id);
        }
        #endregion

        #region 获取基础对象方法

        /// <summary>
        /// 获取通用数据库连接对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbConnection GetDbConnection()
        {
            DbConnection conn = new SQLiteConnection();
            conn.ConnectionString = ConnectionString;
            return conn;
        }

        /// <summary>
        /// 获取通用数据库命令对象
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public DbCommand GetDbCommand()
        {
            return GetDbConnection().CreateCommand();
        }
        #endregion

        #region 获取SQL命令语句方法

        /// <summary>
        /// 获取表说明文档
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string SQL_GetShowTableCommonSql(string table)
        {
            return "select f.value as Common from sysobjects d left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where d.name='{0}'".Fmt(table);
        }

        /// <summary>
        /// 获取表结构
        /// <para>主要获取sql字段的值(create table语句)</para>
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public string Sql_GetShowStructureSql(string table)
        {
            return string.Format("select sql from sqlite_master where type='table' and name='{0}';", table);
        }

        /// <summary>
        /// 获取显示所有表的命令
        /// <para>获取name字段的值</para>
        /// </summary>
        /// <param name="providerName"></param>
        /// <returns></returns>
        public string SQL_GetShowTablesSql()
        {
            return "select * from sqlite_master where type='table';";
        }

        /// <summary>
        /// 获取显示所有存储过程的命令
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowProceduresSql()
        {
            return "select [name] from [sysobjects] where [xtype]='p'";
        }

        /// <summary>
        /// 获取显示所有视图的命令
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowViewsSql()
        {
            return "select [name] from [sysobjects] where [xtype]='v'";
        }

        /// <summary>
        /// 获取显示所有数据库的命令
        /// </summary>
        /// <returns></returns>
        public string SQL_GetShowDatabasesSql()
        {
            if (string.IsNullOrEmpty(this.ConnectionString)) return string.Empty;
            IList<string> list = RegExp.Match(this.ConnectionString, "data srouce=[^;]+");
            return list.Count > 0 ? list[0] : string.Empty;
        }

        /// <summary>
        /// 获取显示数据表所有字段的命令语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string SQL_GetShowFieldsSql(string tableName)
        {
            return "select sql from sqlite_master where type='table' and name='{0}'".Fmt(tableName); ;
        }

        /// <summary>
        /// 获取对应数据库的自动增长字段语句
        /// </summary>
        /// <returns></returns>
        public string Sql_GetAutoIncrementSql()
        {
            return "identity(1,1)";
        }

        /// <summary>
        /// 获取对应数据库前N条记录的语句
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">要查找的字段，如 title,content,*等</param>
        /// <param name="condition">查询条件</param>
        /// <param name="count">记录数量</param>
        /// <returns></returns>
        public string Sql_GetSelectTopNRecordSql(string tableName, string fields = "*", string condition = "", int count = 1)
        {
            return "select {0} from {1} {2} limit {3}".Fmt(fields, tableName, condition, count);
        }

        /// <summary>
        /// 获取删除表命令
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public string Sql_GetDropTableSql(string tablename)
        {
            return string.Format("DROP TABLE IF  EXISTS {0};{1}", tablename, Environment.NewLine);
        }

        /// <summary>
        /// 获取重命名表的命令
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public string Sql_GetRenameTableSql(string tableName, string newName)
        {
            return string.Format("alter table {0} rename to {1};", tableName, newName);
        }

        /// <summary>
        /// 设置查询条件语句,建议查询条件无需带where关键词
        /// <para>包含符号=, is , like ,&gt;,&lt;,!&gt;等关键词自动补齐 where 关键词</para>
        /// <para>不包含符号且不带where 自动补齐 order by关键词</para>
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public string Sql_SetConditionSql(string condition)
        {
            if (condition.Length > 12 && condition.TrimStart().ToLower().Substring(0, 7) == "select ") return condition;
            condition = condition.Trim();
            if (condition.Length == 0) return string.Empty;
            int iSpace = condition.IndexOf(' ');
            bool hasSymbol = false;
            foreach (string s in new string[] { "=", ">", "<", "!", " is ", " like ", " in " })
            {
                if (condition.ToLower().IndexOf(s) > -1) hasSymbol = true;
            }
            if (iSpace == -1)
            {
                return hasSymbol ? " where {0} ".Fmt(condition) : " order by {0} ".Fmt(condition);
            }
            string word = condition.Substring(0, iSpace).ToLower();
            if (word == "where" || word == "order")
            {
                return condition;
            }
            return hasSymbol ? " where {0} ".Fmt(condition) : " order by {0} ".Fmt(condition);
        }
        public string Sql_SetConditionSql(string where, string orderby)
        {
            StringBuilder sb = new StringBuilder();
            if (where.Length > 0) sb.AppendFormat("where {0}", where);
            if (orderby.Length > 0) sb.AppendFormat("order by {0}", orderby);
            return sb.ToString();
        }

        /// <summary>
        /// 设置数量限制
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public string SQL_SetLimit(string sql, int count)
        {
            sql = sql.TrimStart();
            if (sql.Substring(0, 7).ToLower() != "select ") return sql;
            return "{0} limit {1}".Fmt(sql, count);
        }

        public string sql_GetTruncateTableSql(string tableName)
        {
            return string.Format("delete from '{0}';select * from sqlite_sequence; update sqlite_sequence set seq=0 where name='{0}';", tableName);
        }

        /// <summary>
        /// 获取数据库中所有数据表
        /// </summary>
        /// <param name="connectionInfo"></param>
        /// <returns></returns>
        public IList<string> SO_GetTables()
        {
            List<string> list = new List<string>();
            using (IDataReader IDr = ExecuteDataTable(SQL_GetShowTablesSql()).CreateDataReader())
            {
                while (IDr.Read())
                {
                    list.Add(IDr["Name"].ToString());
                }
            }
            list.Sort();
            return list;
        }

        /// <summary>
        /// 获取数据库中所有存储过程
        /// </summary>
        /// <returns></returns>
        public IList<string> SO_GetProcedures()
        {
            List<string> list = new List<string>();
            using (IDataReader IDr = ExecuteDataTable(SQL_GetShowProceduresSql()).CreateDataReader())
            {
                while (IDr.Read())
                {
                    list.Add(IDr[0].ToString());
                }
            }
            list.Sort();
            return list;
        }

        /// <summary>
        /// 获取数据库中所有视图
        /// </summary>
        /// <returns></returns>
        public IList<string> SO_GetViews()
        {
            List<string> list = new List<string>();
            using (IDataReader IDr = ExecuteDataTable(SQL_GetShowViewsSql()).CreateDataReader())
            {
                while (IDr.Read())
                {
                    list.Add(IDr[0].ToString());
                }
            }
            list.Sort();
            return list;
        }

        /// <summary>
        /// 获取指定数据表的所有字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public IList<string> SO_GetFields(string tableName)
        {
            string sql = GetValue(SQL_GetShowFieldsSql(tableName), string.Empty).ToString();
            // CREATE TABLE tconnections (ID INTEGER PRIMARY KEY, Name text, HostName text, Port int, LoginID text, Password text, Database text, ProductName text,Ordinal smallint not null default 999,IsDefault bit, CreatedTime DateTime not null default CURRENT_TIMESTAMP)
            sql = sql.Substring(sql.IndexOf('(') + 1).TrimEnd(')');
            IList<string> list = new List<string>();
            foreach (string f in sql.Split(','))
            {
                list.Add(f.GetFirstWord());
            }
            return list;
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="connInfo"></param>
        /// <param name="tablenames"></param>
        public void SO_DropTable(params string[] tablenames)
        {
            foreach (string s in tablenames)
            {
                ExecuteNonQuery(Sql_GetDropTableSql(s));
            }
        }

        /// <summary>
        /// 确保数据库存在,如果不存在则创建
        /// </summary>
        /// <param name="dbName"></param>
        /// <remarks>2014-4-9</remarks>
        public bool So_EnsureDatabase(string dbName, out string msg)
        {
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "use " + dbName;
                    conn.Open();
                    try
                    {
                        cmd.ExecuteScalar();
                        msg = string.Empty;
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            cmd.CommandText = string.Format("create database  {0}", dbName);
                            cmd.ExecuteNonQuery();
                            msg = string.Format("重建数据库 {0} 成功!", dbName);
                            return true;
                        }
                        catch (Exception ex)
                        {
                            msg = string.Format("重建数据库 {0} 失败: {1}.", dbName, ex.Message);
                            return false;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 确保数据表存在,如果不存在则创建
        /// <para>如：SO_EnsureTable("tMyTable","ID int identity(1,1) primary key, Name varchar(32), State smallint not null default 0")</para>
        /// </summary>
        /// <param name="tablename"></param>
        /// <param name="fieldsdefinesql"></param>
        /// <remarks>2014-2-21</remarks>
        public bool SO_EnsureTable(string tablename, string fieldsdefinesql, out string msg)
        {
            TableName = tablename;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select top 1 * from " + tablename;
                    conn.Open();
                    try
                    {
                        cmd.ExecuteScalar();
                        msg = string.Empty;
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            cmd.CommandText = string.Format("create table {0} ({1})", tablename, fieldsdefinesql);
                            cmd.ExecuteNonQuery();
                            msg = string.Format("重建数据表 {0} 成功!", tablename);
                            return true;
                        }
                        catch (Exception ex)
                        {
                            msg = string.Format("重建数据表 {0} 失败: {1}.", tablename, ex.Message);
                            return false;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 确保数据字段存在,如果不存在则创建
        /// <para>如：</para>
        /// <para>DBH.TableName="tMyTable" (如果已经调用过SO_EnsureTable,则默认数据表是SO_EnsureTable传递的数据表参数)</para>
        /// <para>SO_EnsureField("MyFieldName","datetime not null default getdate()");</para>
        /// <para>此方法会默认判断数据表是否存在,因此无需担心数据表不存在而出错</para>
        /// </summary>
        /// <param name="fieldname"></param>
        /// <param name="fielddefinesql"></param>
        /// <param name="tablename"></param>
        /// <remarks>2014-2-21</remarks>
        public bool SO_EnsureField(string fieldname, string fielddefinesql, out string msg, string tablename = null)
        {
            if (string.IsNullOrEmpty(tablename)) tablename = TableName;
            using (DbConnection conn = GetDbConnection())
            {
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select * from " + tablename + " limit 1;";
                    conn.Open();
                    try
                    {
                        cmd.ExecuteScalar();
                    }
                    catch
                    {
                        msg = string.Format("添加字段 {0} 失败,原因是：数据表 {1} 不存在!", fieldname, tablename);
                        return false;
                    }
                    try
                    {
                        cmd.CommandText = string.Format("select {0} from {1}", fieldname, tablename);
                        cmd.ExecuteScalar();
                        msg = string.Empty;
                        return true;
                    }
                    catch
                    {
                        try
                        {
                            cmd.CommandText = string.Format("alter table {0} add {1} {2}", tablename, fieldname, fielddefinesql);
                            cmd.ExecuteNonQuery();
                            msg = string.Format("重建字段 {0}.{1} {2} 成功!", tablename, fieldname, fielddefinesql);
                            return true;
                        }
                        catch (Exception ex)
                        {
                            msg = string.Format("重建字段 {0}.{1} 失败: {2}.", tablename, fieldname, ex.Message);
                            return false;
                        }
                    }

                }
            }
        }

        /// <summary>
        /// 复制数据库结构和数据
        /// </summary>
        /// <param name="connInfoFrom"></param>
        /// <param name="connInfoTo"></param>
        public void SO_CopyDatabase()
        {
            string sqlschema = SO_GetCreateTableStructures().Values.ToString("");
            string sqldata = SO_ExportTableDataAsSqls(SO_GetTables().ToArray()).Values.ToString("");
            SO_DropTable(SO_GetTables().ToArray());
            ExecuteNonQuery(sqlschema);
            ExecuteNonQuery(sqldata);
        }

        /// <summary>
        /// 获取所有数据库列表
        /// </summary>
        /// <returns></returns>
        public IList<string> SO_GetDatabases()
        {
            return new List<string>() { };
        }
        #endregion

        #region 其他转换操作

        /// <summary>
        /// 托管环境数据库类型名称转换为产品环境数据库类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="clrTypeName"></param>
        /// <returns></returns>
        public string FieldTypeNameToDbTypeName(string clrTypeName)
        {
            clrTypeName = clrTypeName.ToLower();
            switch (clrTypeName)
            {
                case "int32": return "int";
                case "string": return "nvarchar(MAX)";
                case "int16": return "smallint";
                case "boolean": return "bit";
                case "guid": return "uniqueidentifier";
                case "datetime": return "datetime";
                case "decimal": return "money";
                case "int64": return "bigint";
                case "byte[]": return "image";
                case "single": return "real";
                case "double": return "float";
                case "datetimeoffset": return "datetimeoffset(7)";
                case "timespan": return "time(7)";
                case "system.xml.linq.xelement": return "xml";
            }
            return clrTypeName;
        }

        /// <summary>
        /// 产品环境数据库类型名称转换为托管环境数据库类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="clrTypeName"></param>
        /// <returns></returns>
        public string DbTypeNameToFieldTypeName(string clrTypeName, bool isNullable = true)
        {
            clrTypeName = clrTypeName.ToLower();
            switch (clrTypeName)
            {
                case "nvarchar":
                case "varchar":
                case "ntext":
                case "text":
                case "nchar":
                case "char":
                case "xml": return "string";
                case "int": return isNullable ? "int?" : "int";
                case "smallint": return isNullable ? "short?" : "short";
                case "uniqueidentifier": return isNullable ? "Guid?" : "Guid";
                case "tinyint": return isNullable ? "byte?" : "byte";
                case "datetime":
                case "smalldatetime":
                case "timestamp": return isNullable ? "DateTime?" : "DateTime";
                case "bit": return isNullable ? "bool?" : "bool";
                case "money":
                case "smallmoney": return isNullable ? "decimal?" : "decimal";
                case "float": return isNullable ? "double?" : "double";
                case "real": return isNullable ? "single?" : "single";
                case "varbinary":
                case "binary":
                case "image": return "byte[]";
                case "bigint": return isNullable ? "long?" : "long";
                case "datetimeoffset(7)": return "datetimeoffset";
                case "time(7)": return "timespan";
                case "variant": return "object";
                default:
                    return clrTypeName;

            }
        }

        /// <summary>
        /// 托管环境数据库数据类型转换成C#数据类型
        /// </summary>
        /// <param name="fieldTypeName">数据库字段类型</param>
        /// <param name="isNullable">是否允许null</param>
        /// <returns>C#数据类型</returns>
        public string FieldTypeToClrTypeName(string fieldTypeName, bool isNullable = true)
        {
            string sOut = fieldTypeName;
            switch (fieldTypeName.ToLower())
            {
                case "string": return "string";
                case "int32": return isNullable ? "int?" : "int";
                case "int16": return isNullable ? "short?" : "short";
                case "byte": return isNullable ? "byte?" : "byte";
                case "datetime": return isNullable ? "DateTime?" : "DateTime";
                case "boolean": return isNullable ? "bool?" : "bool";
                case "decimal": return isNullable ? "decimal?" : "decimal";
                case "double": return isNullable ? "double?" : "double";
                case "guid": return isNullable ? "Guid?" : "Guid";
                case "byte[]": return "byte[]";
                case "int64": return isNullable ? "long?" : "long";
                default:
                    return string.Format("{0}", sOut);
            }
        }

        /// <summary>
        /// C#数据类型转换为托管环境数据库数据类型
        /// </summary>
        /// <param name="clrType"></param>
        /// <returns></returns>
        public DbType ClrTypeToDbType(Type clrType)
        {
            if (clrType.IsGType<int>()) return DbType.Int32;
            if (clrType == typeof(string) || clrType.IsGType<DateTime>()) return DbType.String;
            if (clrType.IsGType<double>()) return DbType.Double;
            if (clrType.IsGType<bool>()) return DbType.Boolean;
            if (clrType.IsGType<short>()) return DbType.Int16;
            if (clrType.IsGType<Guid>()) return DbType.Guid;
            if (clrType.IsGType<decimal>()) return DbType.Decimal;
            if (clrType == typeof(System.Xml.Linq.XElement)) return DbType.Xml;
            if (clrType == typeof(byte[])) return DbType.Binary;
            if (clrType == typeof(object)) return DbType.Object;
            if (clrType.IsGType<long>()) return DbType.Int64;
            return DbType.String;
        }

        /// <summary>
        /// C#数据类型转为SQL数据类型名称
        /// </summary>
        /// <param name="providerName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public string ClrTypeToDbTypeName(Type type)
        {
            if (type.IsGType<int>()) return "int";
            if (type == typeof(string)) return "nvarchar";
            if (type.IsGType<short>()) return "smallint";
            if (type.IsGType<DateTime>()) return "datetime";
            if (type.IsGType<bool>()) return "bit";
            if (type.IsGType<Guid>()) return "uniqueidentifier";
            if (type == typeof(byte[])) return "image";
            if (type.IsGType<double>()) return "float";
            if (type.IsGType<decimal>()) return "money";
            if (type == typeof(System.Xml.Linq.XElement)) return "xml";
            if (type.IsGType<byte>()) return "tinyint";
            if (type.IsGType<long>()) return "bigint";
            if (type.IsGType<TimeSpan>()) return "time(7)";
            if (type.IsGType<DateTimeOffset>()) return "datetimeoffset(7)";
            if (type.IsGType<Single>()) return "real";
            return type.ToString().ToLower();
        }

        /// <summary>
        /// 格式化为指定数据库产品的sql命令
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string SQL_FormatSql(string sql)
        {
            return sql;
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="dbPath"></param>
        /// <returns></returns>
        public string GetConnectionString(string dbPath, string a = "", string b = "", string c = "")
        {
            return "Data Source={0}; Pooling=true; FailIfMissing=false".Fmt(dbPath);
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        public bool TestConnection(out string message)
        {
            using (DbConnection conn = GetDbConnection())
            {
                try
                {
                    conn.Open();
                    message = "连接成功";
                    return true;
                }
                catch (DbException e)
                {
                    message = e.Message;
                    return false;
                }
            }
        }
        #endregion

        #region 存储过程操纵方法

        /// <summary>
        /// 执行存储过程,返回新的参数值集合
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public Dictionary<string, object> ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters)
        {
            int iOut = 0;
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.Parameters.AddRange(dbParameters);
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.CommandText = procedureName;
                conn.Open();
                iOut = oCmd.ExecuteNonQuery();
            }
            return dbParameters.ToDictionary();
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters)
        {
            DataTable oDt = new DataTable();
            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = procedureName;
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.Parameters.AddRange(dbParameters);
                DbDataAdapter oDa = new SQLiteDataAdapter();
                oDa.SelectCommand = oCmd;
                conn.Open();
                oDa.Fill(oDt);
            }
            if (outParameters == null) return oDt;
            foreach (DbParameter para in dbParameters)
            {
                outParameters[para.ParameterName] = para.Value;
            }
            return oDt;
        }

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters)
        {
            Dictionary<string, object> dict = null;
            return this.ExecuteProcedureDataTable(procedureName, ref dict, dbParameters);
        }

        #endregion

        #region 备份还原

        /// <summary>
        /// 备份整个数据库
        /// </summary>
        /// <returns></returns>
        public void SO_BackupDatabase()
        {

        }

        /// <summary>
        /// 还原整个数据库
        /// </summary>
        /// <param name="schemasql"></param>
        /// <param name="datasql"></param>
        public void SO_RebackDatabase(string schemasql, string datasql)
        {
            if (schemasql.Length > 0)
            {
                SO_DropTable(SO_GetTables().ToArray());
                this.ExecuteNonQuery(schemasql);
            }
            if (datasql.Length > 0) this.ExecuteNonQuery(datasql);
        }

        /// <summary>
        /// 获取创建表的语句列表
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_GetCreateTableStructures(params string[] tableNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            IList<string> lstTables = tableNames.Length == 0 ? SO_GetTables() : tableNames;
            using (DbConnection conn = GetDbConnection())
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendFormat("-- Created By DBKit {0}{1}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), Environment.NewLine);
                DbCommand cmd = conn.CreateCommand();
                conn.Open();
                foreach (string tbl in lstTables)
                {
                    cmd.CommandText = Sql_GetShowStructureSql(tbl);
                    using (IDataReader idr = cmd.ExecuteReader())
                    {
                        while (idr.Read())
                        {
                            sb.AppendLine(idr["sql"].ToString());
                        }
                    }
                    sb.AppendLine();
                    dict.Add(tbl, sb.ToString());
                    sb.Clear();
                }
                cmd.Dispose();
            }
            return dict;
        }

        /// <summary>
        /// 获取创建存储过程的语句列表
        /// </summary>
        /// <param name="procNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_GetCreateProcedureStructures(params string[] procNames)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            StringBuilder sb;
            IDataReader idr;
            string sFmt = SQL_FormatSql("`{0}`");
            IList<string> lstTables = procNames.Length == 0 ? SO_GetProcedures() : procNames;
            foreach (string s in lstTables)
            {
                sb = new StringBuilder();
                idr = ExecuteDataTable(string.Format("exec sp_helptext '{0}'", s)).CreateDataReader();
                while (idr.Read())
                {
                    sb.AppendLine(idr[0].ToString());
                }
                dict.Add(s, sb.ToString());
            }
            return dict;
        }

        /// <summary>
        /// 获取创建视图的语句列表
        /// </summary>
        /// <param name="viewNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_GetCreateViewStructures(params string[] viewNames)
        {
            return SO_GetCreateProcedureStructures(viewNames);
        }

        /// <summary>
        /// 获取从模型更新数据库的语句
        /// <para>2013-8-2 by sutroon</para>
        /// </summary>
        /// <param name="doc"></param>
        /// <returns></returns>
        public string Adv_GetTableSchemaUpdateSqlByXml(XElement doc, params string[] tableNames)
        {
            XElement docDb = XElement.Parse(string.Format("<?xml version=\"1.0\" encoding=\"utf-8\"?><db>{0}</db>", SO_ExportTableStructureAsXmls()));
            // 模型中的所有表名
            IEnumerable<string> lstTablesXml = from t in doc.Document.Elements() select t.Attribute("Name").Value;
            // 数据库中的所有表名
            IEnumerable<string> lstTablesDb = from t in docDb.Document.Elements() select t.Attribute("Name").Value;
            // 模型和数据库中表的交集
            IEnumerable<string> lstTableIntersect = lstTablesXml.Intersect(lstTablesDb);
            StringBuilder sb = new StringBuilder();
            XElement xTableXml, xTableDb, xField, xFieldtmp;
            IEnumerable<string> lstFieldsXml, lstFieldsDb, lstFieldsAdd, lstFieldsDrop, lstFieldsIntersect;
            foreach (string tbl in lstTableIntersect)
            {
                xTableXml = doc.Document.Elements().Single(a => a.Attribute("Name").Value == tbl);
                xTableDb = docDb.Document.Elements().Single(a => a.Attribute("Name").Value == tbl);
                lstFieldsXml = from f in xTableXml.Elements() select f.Attribute("Name").Value;
                lstFieldsDb = from f in xTableDb.Elements() select f.Attribute("Name").Value;
                // 数据库中有的而模型没有的则移除
                lstFieldsDrop = lstFieldsDb.Except(lstFieldsXml);
                foreach (string f in lstFieldsDrop)
                {
                    sb.AppendFormat("alter table [{0}] drop {1};{2}", tbl, f, Environment.NewLine);
                }
                // 数据库中没有而模型有的则增加
                lstFieldsAdd = lstFieldsXml.Except(lstFieldsDb);
                foreach (string f in lstFieldsAdd)
                {
                    xField = xTableXml.Elements().Single(a => a.Attribute("Name").Value == f);
                    sb.AppendFormat("alter table [{0}] add {1} {2}{3} {4} {5};{6}", tbl, f, xField.Attribute("Type").Value, (xField.Attribute("Type").Value.IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + xField.Attribute("Length").Value + ")" : ""), (xField.Attribute("Null").Value == "0" ? "not null" : ""), (xField.Attribute("Default").Value.Length == 0 ? "" : "default " + xField.Attribute("Default").Value), Environment.NewLine);
                }
                // 模型和数据库中字段的交集
                lstFieldsIntersect = lstFieldsDb.Intersect(lstFieldsXml);
                foreach (string f in lstFieldsIntersect)
                {
                    xField = xTableXml.Elements().Single(a => a.Attribute("Name").Value == f);
                    xFieldtmp = xTableDb.Elements().Single(a => a.Attribute("Name").Value == f);
                    if (xField.Attribute("Type") != xFieldtmp.Attribute("Type"))
                    {
                        sb.AppendFormat("alter table [{0}] alter column {1} {2}{3} {4} {5};{6}", tbl, f, xField.Attribute("Type").Value, (xField.Attribute("Type").Value.IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + xField.Attribute("Length").Value + ")" : ""), (xField.Attribute("Null").Value == "0" ? "not null" : ""), (xField.Attribute("Default").Value.Length == 0 ? "" : "default " + xField.Attribute("Default").Value), Environment.NewLine);
                    }
                }

            }
            // 数据库中有而模型没有的表则丢弃
            sb.AppendLine(lstTablesDb.Except(lstTablesXml).ToString(Environment.NewLine, "drop table [{0}];"));
            // 数据库中没有而模型中有的表则创建
            IEnumerable<string> lstTableCreate = lstTablesXml.Except(lstTablesDb);
            if (lstTableCreate.Count() > 0)
            {
                sb.AppendLine(SO_GetCreateTableStructureSqlsByXml(doc, lstTableCreate.ToArray()));
            }
            return sb.ToString();
        }
        public string SO_GetCreateTableStructureSqlsByXml(XElement doc, params string[] tableNames)
        {
            // 模型中的所有表名
            IEnumerable<string> lstTableCreate = null;
            if (tableNames.Length == 0)
            {
                lstTableCreate = from t in doc.Elements() select t.Attribute("Name").Value;
            }
            else
            {
                lstTableCreate = from t in doc.Elements() where tableNames.Contains(t.Attribute("Name").Value) select t.Attribute("Name").Value;
            }
            XElement xTableXml;
            StringBuilder sb = new StringBuilder();
            foreach (string tbl in lstTableCreate)
            {
                xTableXml = doc.Elements().Single(a => a.Attribute("Name").Value == tbl);
                sb.AppendFormat("create table [{0}]{1}", tbl, Environment.NewLine);
                sb.AppendLine("(");
                foreach (XElement f in xTableXml.Elements())
                {
                    if (f.Attribute("Key").Value == "1")
                    {
                        sb.AppendFormat("\t[{0}] {1} primary key{2}{3};{4}", f.Attribute("Name").Value, f.Attribute("Type").Value, (f.Attribute("Type").Value == "int" ? " identity(1,1)" : ""), (f.Attribute("Default").Value.Length == 0 ? "" : " default " + f.Attribute("Default").Value), Environment.NewLine);
                    }
                    else
                    {
                        sb.AppendFormat("\t[{0}] {1}{2} {3} {4};{5}", f.Attribute("Name").Value, f.Attribute("Type").Value, (f.Attribute("Type").Value.IsIn("varchar", "nvarchar", "char", "nchar") ? "(" + f.Attribute("Length").Value + ")" : ""), (f.Attribute("Null").Value == "0" ? "not null" : ""), (f.Attribute("Default").Value.Length == 0 ? "" : "default " + f.Attribute("Default").Value), Environment.NewLine);
                    }
                }
                sb.AppendLine(");");
                sb.AppendLine();
            }
            return sb.ToString();
        }

        /// <summary>
        /// 获取特性数据库模型XML文档
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_ExportTableStructureAsXmls(params string[] tableNames)
        {
            DataTable dtFields;
            StringBuilder sb = new StringBuilder();
            Dictionary<string, string> dict = new Dictionary<string, string>();
            foreach (string table in tableNames)
            {
                dtFields = CreateTableSqlToSchemaDataTable(GetValue(SQL_GetShowFieldsSql(table), string.Empty).ToString());
                sb.AppendFormat("<table Name=\"{0}\">", table);
                foreach (DataRow r in dtFields.Rows)
                {
                    sb.AppendFormat("<field Name=\"{0}\" Type=\"{1}\" Length=\"{2}\" Null=\"{3}\" Key=\"{4}\" Default=\"{5}\" Extra=\"{6}\" Common=\"{7}\" />", r["Name"], r["Type"], r["Length"], r["Null"], r["Key"], r["Default"], "", r["Common"]);
                }
                sb.AppendFormat("</table>");
                dict.Add(table, sb.ToString());
                sb.Clear();
            }
            return dict;
        }

        /// <summary>
        /// 获取数据表结构
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private DataTable CreateTableSqlToSchemaDataTable(string sql)
        {
            //CREATE TABLE tconnections (ID INTEGER PRIMARY KEY, Name text, HostName text, Port int, LoginID text, Password text, Database text, ProductName text,Ordinal smallint not null default 999,IsDefault bit, CreatedTime DateTime not null default CURRENT_TIMESTAMP)
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Type");
            dt.Columns.Add("Length");
            dt.Columns.Add("Null");
            dt.Columns.Add("Key");
            dt.Columns.Add("Default");
            dt.Columns.Add("Extra");
            dt.Columns.Add("Common");
            sql = sql.Substring(sql.IndexOf('(') + 1);
            sql = sql.TrimEnd(')');
            string[] arrf;
            foreach (string f in sql.Split(','))
            {
                if (f.Length == 0) continue;
                arrf = f.Trim().Split(' ');
                if (f.ToLower().Contains("primary key"))
                {
                    dt.Rows.Add(arrf[0], arrf[1], 0, 0, 1, "", "", "");
                }
                else
                {
                    dt.Rows.Add(arrf[0], arrf[1], 0, f.ToLower().Contains("not null") ? 0 : 1, 0, f.Contains(" default ") ? f.Substring(f.IndexOf(" default ") + 8) : "", "", "");
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取标准数据库模型XML文档
        /// </summary>
        /// <param name="tableNames"></param>
        /// <returns></returns>
        public Dictionary<string, string> SO_ExportTableStructureAsStandardXmls(params string[] tableNames)
        {
            string slen, stype;
            DataTable dtFields;
            StringBuilder sb = new StringBuilder();
            Dictionary<string, string> dict = new Dictionary<string, string>();
            foreach (string table in tableNames)
            {
                sb.AppendFormat("<table Name=\"{0}\">", table);
                dtFields = this.ExecuteDataTable(Sql_GetShowStructureSql(table));
                foreach (DataRow r in dtFields.Rows)
                {
                    sb.AppendFormat("<field Name=\"{0}\" Type=\"{1}\" Length=\"{2}\" Null=\"{3}\" Key=\"{4}\" Default=\"{5}\" Extra=\"{6}\" Common=\"{7}\" />", r["Name"], r["DbType"], r["Precision"], r["AllowDBNull"], r["KeyName"] == DBNull.Value ? "0" : "1", r["DefaultValue"], "", r["Common"]);
                }
                sb.AppendFormat("</table>");
                dict.Add(table, sb.ToString());
                sb.Clear();
            }
            return dict;
        }
        #endregion

        /// <summary>
        /// Sql 语法检查 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string SO_ParseSql(string sql)
        {
            if (string.IsNullOrEmpty(sql)) return "命令无效";
            sql = string.Format("SET PARSEONLY ON;{0}", sql);

            using (DbConnection conn = GetDbConnection())
            {
                DbCommand oCmd = conn.CreateCommand();
                oCmd.CommandText = sql;
                conn.Open();
                try
                {
                    this.ExecuteNonQuery(sql);
                }
                catch (DbException de)
                {
                    return de.Message;
                }
            }
            return string.Empty;
        }


        public string SO_GetUpdateTableStructureSqlsByXml(XElement doc, params string[] tableNames)
        {
            throw new NotImplementedException();
        }

        public string SQL_GetCreateTableSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetCreateProcedureSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetRenameProcedureStructureSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetDropProcedureSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetExecProcedureSql(string itemName)
        {
            throw new NotImplementedException();
        }

        public string SQL_GetCreateViewSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetRenameViewSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetDropViewSql()
        {
            throw new NotImplementedException();
        }

        public string SQL_GetExecViewSql(string itemName)
        {
            throw new NotImplementedException();
        }
    }
}
